package de.haw.dbp.a4.logic;

import dc.connectionmanager.logic.ACommand;
import dc.connectionmanager.logic.CArgument;
import dc.connectionmanager.logic.ICallback;
import dc.connectionmanager.logic.IConnectionManager;

public class DBCommand extends ACommand 
{

	public DBCommand(IConnectionManager cmHandle) 
	{
		super(cmHandle);
	}

	public void exec_query(ICallback cb, String query)
	{
		this.sendCommand(new CArgument(cb, query));
	}
        
        public void getAllMedia(ICallback cb)
        {
            this.sendCommand(new CArgument(cb, "SELECT m.mediumname as Medium, mt.name as Typ, m.hersteller FROM medium m INNER JOIN medientyp mt ON m.mtyp = mt.medientyp_id"));
        }
        
        public void getMediaByMediumName(ICallback cb, String name)
        {
            this.sendCommand(new CArgument(cb, "SELECT m.mediumname as Medium, mt.name as Typ, m.hersteller FROM medium m INNER JOIN medientyp mt ON m.mtyp = mt.medientyp_id WHERE lower(m.mediumname) LIKE '%"+name.toLowerCase()+"%'"));
        }
        
        public void getMediaByMediumNameAndMTyp(ICallback cb, String name, String mtyp_id)
        {
            this.sendCommand(new CArgument(cb, "SELECT m.mediumname as Medium, mt.name as Typ, m.hersteller FROM medium m INNER JOIN medientyp mt ON m.mtyp = mt.medientyp_id WHERE lower(m.mediumname) LIKE '%"+name.toLowerCase()+"%' AND mt.medientyp_id = "+mtyp_id));
        }
        
        public void getAllAusleihe(ICallback cb)
        {
            this.sendCommand(new CArgument(cb, "SELECT m.mediumname as Medium, mt.name as Typ, p.personname as Person, f.firmaname as Firma, a.ausleihdatum as Ausgeliehen, a.rueckgabedatum as Zurückgegeben FROM ausleihe a INNER JOIN person p ON a.person = p.person_id LEFT OUTER JOIN firma f ON a.firma = f.firma_id INNER JOIN medium m ON a.medium = m.medien_id INNER JOIN medientyp mt ON m.mtyp = mt.medientyp_id"));
        }
        
        public void getAusleiheByMediumName(ICallback cb, String name)
        {
            this.sendCommand(new CArgument(cb, "SELECT m.mediumname as Medium, mt.name as Typ, p.personname as Person, f.firmaname as Firma, a.ausleihdatum as Ausgeliehen, a.rueckgabedatum as Zurückgegeben FROM ausleihe a INNER JOIN person p ON a.person = p.person_id LEFT OUTER JOIN firma f ON a.firma = f.firma_id INNER JOIN medium m ON a.medium = m.medien_id INNER JOIN medientyp mt ON m.mtyp = mt.medientyp_id WHERE (lower(m.mediumname) LIKE '%"+name.toLowerCase()+"%' OR lower(f.firmaname) LIKE '%"+name.toLowerCase()+"%')"));
        }
        
        public void getAusleiheByMediumNameWithFirma(ICallback cb, String name)
        {
            this.sendCommand(new CArgument(cb, "SELECT m.mediumname as Medium, mt.name as Typ, p.personname as Person, f.firmaname as Firma, a.ausleihdatum as Ausgeliehen, a.rueckgabedatum as Zurückgegeben FROM ausleihe a INNER JOIN person p ON a.person = p.person_id INNER JOIN firma f ON a.firma = f.firma_id INNER JOIN medium m ON a.medium = m.medien_id INNER JOIN medientyp mt ON m.mtyp = mt.medientyp_id WHERE (lower(m.mediumname) LIKE '%"+name.toLowerCase()+"%' OR lower(f.firmaname) LIKE '%"+name.toLowerCase()+"%')"));
        }
        
        public void getAusleiheByMediumNameWithoutFirma(ICallback cb, String name)
        {
            this.sendCommand(new CArgument(cb, "SELECT m.mediumname as Medium, mt.name as Typ, p.personname as Person, f.firmaname as Firma, a.ausleihdatum as Ausgeliehen, a.rueckgabedatum as Zurückgegeben FROM ausleihe a INNER JOIN person p ON a.person = p.person_id LEFT OUTER JOIN firma f ON a.firma = f.firma_id INNER JOIN medium m ON a.medium = m.medien_id INNER JOIN medientyp mt ON m.mtyp = mt.medientyp_id WHERE (lower(m.mediumname) LIKE '%"+name.toLowerCase()+"%' OR lower(f.firmaname) LIKE '%"+name.toLowerCase()+"%') AND f.firmaname IS NULL"));
        }
        
        public void getAusleiheByMediumNameAndMTyp(ICallback cb, String name, String mtyp_id)
        {
            this.sendCommand(new CArgument(cb, "SELECT m.mediumname as Medium, mt.name as Typ, p.personname as Person, f.firmaname as Firma, a.ausleihdatum as Ausgeliehen, a.rueckgabedatum as Zurückgegeben FROM ausleihe a INNER JOIN person p ON a.person = p.person_id LEFT OUTER JOIN firma f ON a.firma = f.firma_id INNER JOIN medium m ON a.medium = m.medien_id INNER JOIN medientyp mt ON m.mtyp = mt.medientyp_id WHERE (lower(m.mediumname) LIKE '%"+name.toLowerCase()+"%' OR lower(f.firmaname) LIKE '%"+name.toLowerCase()+"%') AND mt.medientyp_id = "+mtyp_id));
        }
        
        public void getAusleiheByMediumNameAndMTypWithFirma(ICallback cb, String name, String mtyp_id)
        {
            this.sendCommand(new CArgument(cb, "SELECT m.mediumname as Medium, mt.name as Typ, p.personname as Person, f.firmaname as Firma, a.ausleihdatum as Ausgeliehen, a.rueckgabedatum as Zurückgegeben FROM ausleihe a INNER JOIN person p ON a.person = p.person_id INNER JOIN firma f ON a.firma = f.firma_id INNER JOIN medium m ON a.medium = m.medien_id INNER JOIN medientyp mt ON m.mtyp = mt.medientyp_id WHERE (lower(m.mediumname) LIKE '%"+name.toLowerCase()+"%' OR lower(f.firmaname) LIKE '%"+name.toLowerCase()+"%') AND mt.medientyp_id = "+mtyp_id));
        }
        
        public void getAusleiheByMediumNameAndMTypWithoutFirma(ICallback cb, String name, String mtyp_id)
        {
            this.sendCommand(new CArgument(cb, "SELECT m.mediumname as Medium, mt.name as Typ, p.personname as Person, f.firmaname as Firma, a.ausleihdatum as Ausgeliehen, a.rueckgabedatum as Zurückgegeben FROM ausleihe a INNER JOIN person p ON a.person = p.person_id LEFT OUTER JOIN firma f ON a.firma = f.firma_id INNER JOIN medium m ON a.medium = m.medien_id INNER JOIN medientyp mt ON m.mtyp = mt.medientyp_id WHERE (lower(m.mediumname) LIKE '%"+name.toLowerCase()+"%' OR lower(f.firmaname) LIKE '%"+name.toLowerCase()+"%') AND mt.medientyp_id = "+mtyp_id+" AND f.firmaname IS NULL"));
        }
        
        public void getAllMedienTyp(ICallback cb)
        {
            this.sendCommand(new CArgument(cb, "SELECT mt.name FROM medientyp mt ORDER BY mt.medientyp_id ASC"));
        }
        
        public void insertNewMedium(String mName, String mHersteller, int mTyp)
        {
            this.sendCommandStraight("INSERT INTO MEDIUM VALUES ((SELECT MAX(MEDIEN_ID) +1 FROM MEDIUM), '"+mName+"', '"+mHersteller+"', "+mTyp+")");
        }
	

}
